﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Bitirme.DAL.Entities;
using System.Data.SqlClient;
using Bitirme.DAL.Utils;
using System.Data;

namespace Bitirme.DAL.Data_Access
{
    public static class CapabilityCollection
    {
        public static int InsertCapability(Capability capability)
        {

            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parName = null;
            SqlParameter parReturnValue = null;


            int insertedCapabilityID = -1;
            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("InsertCapability", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??

                parName = new SqlParameter();
                parName.ParameterName = "@name";
                parName.Value = capability.Name;
                parName.SqlDbType = SqlDbType.VarChar;

                parReturnValue = new SqlParameter();
                parReturnValue.Direction = ParameterDirection.ReturnValue;
                parReturnValue.ParameterName = "@ID";

                cmd.Parameters.Add(parName);
                cmd.Parameters.Add(parReturnValue);
                insertedCapabilityID = capability.ID;

                myConnection.Open();
                cmd.ExecuteScalar();
                Int32.TryParse(parReturnValue.Value.ToString(), out insertedCapabilityID);
            }
            catch (SqlException e)
            {
                insertedCapabilityID = -1;
                Console.WriteLine(e);
            }
            finally
            {

                if (myConnection != null)
                {

                    myConnection.Close();
                    myConnection.Dispose();
                }

            }
            return insertedCapabilityID;
        }
        public static bool UpdateCapability(Capability capability)
        {

            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parID = null;
            SqlParameter parName = null;


            bool isupdatedCapability = false;
            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("UpdateCapabilityByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??


                parID = new SqlParameter();
                parID.ParameterName = "@ID";
                parID.Value = capability.ID;
                parID.SqlDbType = SqlDbType.Int;

                parName = new SqlParameter();
                parName.ParameterName = "@name";
                parName.Value = capability.Name;
                parName.SqlDbType = SqlDbType.VarChar;

                cmd.Parameters.Add(parID);
                cmd.Parameters.Add(parName);

                isupdatedCapability = true;
                myConnection.Open();
                cmd.ExecuteScalar();
            }
            catch (SqlException e)
            {
                isupdatedCapability = false;
                Console.WriteLine(e);
            }
            finally
            {

                if (myConnection != null)
                {

                    myConnection.Close();
                    myConnection.Dispose();
                }

            }
            return isupdatedCapability;
        }
        public static Boolean DeleteCapabilityByID(int capabilityID)
        {
            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parCapabilityID = null;
            bool isDeleteSuccessful = false;

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("DeleteCapabilityByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parCapabilityID = new SqlParameter();
                parCapabilityID.ParameterName = "@ID";
                parCapabilityID.Value = capabilityID;
                parCapabilityID.SqlDbType = SqlDbType.Int;


                cmd.Parameters.Add(parCapabilityID);


                myConnection.Open();

                cmd.ExecuteScalar();
                isDeleteSuccessful = true;
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
                isDeleteSuccessful = false;
            }

            finally
            {

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return isDeleteSuccessful;
        }
        public static List <Capability> GetAllCapabilities()
        {

            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlDataReader myReader = null;
            List<Capability> capabilities = new List<Capability>();
            Capability capabilityItem;

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("SelectAllCapabilities", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??

                myConnection.Open();
                myReader = cmd.ExecuteReader();
                if (myReader.HasRows)
                {
                    while (myReader.Read())
                    {
                        capabilityItem = new Capability();
                        capabilityItem.ID = myReader.GetInt32(0);
                        capabilityItem.Name = myReader.GetString(1);
                        capabilities.Add(capabilityItem);
                    }
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            finally
            {
                if (myReader != null)
                {
                    myReader.Close();
                    myReader.Dispose();
                }

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return capabilities;
        }
    
    }
}